﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace 学生绩效
{
    public  class ExcelOpe
    {
        public static DataTable ReadMYEXCEL(string finalpath)
        {
            FileStream filestreamwrite = new FileStream(finalpath, FileMode.Open, FileAccess.ReadWrite);
            //2从读取文件流中创建一个HSSFWorkbook
            HSSFWorkbook book = new HSSFWorkbook(filestreamwrite);
            HSSFSheet sheet = book.GetSheetAt(0) as HSSFSheet;
            IRow headerrow = sheet.GetRow(10-1);//获取第10行。成为标题
            //3 获取表格名称
            string sheetname = book.GetSheetName(0);
            var ExcelTable = new DataTable(sheetname);
            ////4 表头
            ICell[] headcells = new ICell[24];
            for (int i = 1; i < 25; i++)
            {
                headcells[i-1] = headerrow.GetCell(i);
                ExcelTable.Columns.Add(headcells[i-1].ToString(), typeof(string));
            }
            int itemtotalcount = sheet.LastRowNum; //有多少行
            for (int i = 5-1; i <= itemtotalcount-1; i++)
            {
                //IRow row = sheet.GetRow(i);
                var NR = ExcelTable.NewRow();
                HSSFRow row = sheet.GetRow(i) as HSSFRow;
                try
                {
                    //将每一行的单元格一次加载到datatable中，每一行添加（0），（1），（2）列。
                    //如果数据量有变化，需要增加
                    HSSFCell[] cells = new HSSFCell[24];
                    for (int j = 1; j < 25; j++)
                    {
                        cells[j-1] = row.GetCell(j) as HSSFCell;
                        if (cells[j - 1].CellType == CellType.Formula)
                        {
                            NR[j - 1] = cells[j - 1].NumericCellValue;
                        }
                        else
                        {
                            NR[j - 1] = cells[j - 1].ToString();
                        }
                        
                    }                    
                    ExcelTable.Rows.Add(NR);
                }
                catch (Exception)
                {
                    break;
                }


            }
            //下列被屏蔽的代码可以用来将datatable数据显示到datagridview控件中，实现无缝显示。
            //dateviewDisplay.DataSource = ExcelTable.AsDataView();
            //foreach (DataGridViewColumn g in dateviewDisplay.Columns)
            //{
            //    g.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //}
            filestreamwrite.Dispose();
            filestreamwrite.Close();
            return ExcelTable;
        }public static DataTable ReadMYEXCELNew(string finalpath)
        {
            FileStream filestreamwrite = new FileStream(finalpath, FileMode.Open, FileAccess.ReadWrite);
            //2从读取文件流中创建一个HSSFWorkbook
            HSSFWorkbook book = new HSSFWorkbook(filestreamwrite);
            HSSFSheet sheet = book.GetSheetAt(1) as HSSFSheet;
            IRow headerrow = sheet.GetRow(10-1);//获取第10行。成为标题
            //3 获取表格名称
            string sheetname = book.GetSheetName(0);
            var ExcelTable = new DataTable(sheetname);
            ////4 表头
            ICell[] headcells = new ICell[24];
            for (int i = 1; i < 25; i++)
            {
                headcells[i-1] = headerrow.GetCell(i);
                ExcelTable.Columns.Add(headcells[i-1].ToString(), typeof(string));
            }
            int itemtotalcount = sheet.LastRowNum; //有多少行
            for (int i = 5-1; i <= itemtotalcount-1; i++)
            {
                //IRow row = sheet.GetRow(i);
                var NR = ExcelTable.NewRow();
                HSSFRow row = sheet.GetRow(i) as HSSFRow;
                try
                {
                    //将每一行的单元格一次加载到datatable中，每一行添加（0），（1），（2）列。
                    //如果数据量有变化，需要增加
                    HSSFCell[] cells = new HSSFCell[24];
                    for (int j = 1; j < 25; j++)
                    {
                        cells[j-1] = row.GetCell(j) as HSSFCell;
                        if (cells[j - 1].CellType == CellType.Formula)
                        {
                            NR[j - 1] = cells[j - 1].NumericCellValue;
                        }
                        else
                        {
                            NR[j - 1] = cells[j - 1].ToString();
                        }
                        
                    }                    
                    ExcelTable.Rows.Add(NR);
                }
                catch (Exception)
                {
                    break;
                }


            }
            //下列被屏蔽的代码可以用来将datatable数据显示到datagridview控件中，实现无缝显示。
            //dateviewDisplay.DataSource = ExcelTable.AsDataView();
            //foreach (DataGridViewColumn g in dateviewDisplay.Columns)
            //{
            //    g.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
            //}
            filestreamwrite.Dispose();
            filestreamwrite.Close();
            return ExcelTable;
        }
        /// <summary>
        /// 返回今年今月的天数
        /// </summary>
        /// <param name="year">今年的年数，如2018</param>
        /// <param name="month">今年当前月份的月数，如3</param>
        /// <returns>这个月的天数</returns>
        private static int DayCountsofMonth(int year, int month)
        {
            int day = 0;
            //year =Convert.ToInt32(DateTime.Now.ToString("YYYY"));
            switch (month)
            {
                case 1: day = 31; break;
                case 2:
                    if (DateTime.IsLeapYear(year))
                    {
                        day = 29;
                    }
                    else
                    {
                        day = 28;
                    }; break;
                case 3: day = 31; break;
                case 4: day = 30; break;
                case 5: day = 31; break;
                case 6: day = 30; break;
                case 7: day = 31; break;
                case 8: day = 31; break;
                case 9: day = 30; break;
                case 10: day = 31; break;
                case 11: day = 30; break;
                case 12: day = 31; break;
                default: break;
            }
            return day;
        }

        /// <summary>
        /// 在程序的启动目录中创建EXCEL数据库
        /// </summary>
        /// <returns></returns>
        public static void CreateMYEXCEL(string number = "序号", string dataItem = "数据", string time = "时间")
        {
            string basePath = Path.Combine(Directory.GetCurrentDirectory(), DateTime.Now.Year + "年读码数据库");
            if (Directory.Exists(basePath))
            {
                return;
            }
            else
            {
                var rootdir_year = System.IO.Directory.CreateDirectory(basePath);//1 创建年份文件夹，如2018年
                for (int i = 1; i <= 12; i++)
                {
                    var subdir_month = rootdir_year.CreateSubdirectory(i.ToString() + "月");
                    for (int j = 1; j <= DayCountsofMonth(DateTime.Now.Year, i); j++)
                    {
                        var subdir_day = subdir_month.CreateSubdirectory(j.ToString() + "日"); //在月份文件夹里面创建日期文件夹
                        string strday = subdir_day.FullName;
                        for (int k = 0; k <= 23; k++) //在日期文件夹中创建小时excel
                        {
                            #region 先创建Excel
                            byte[] b = new byte[1024 * 024 * 5];//先按照时间点，如11点创建excel文件
                            int r = b.Length;
                            string excelpath = Path.Combine(strday, k.ToString() + "点.xls");
                            var filecreate = File.Create(excelpath, r);
                            filecreate.Dispose();
                            #endregion
                            #region 构建Excel内容，并覆盖之前创建好的Excel文件
                            HSSFWorkbook book = new HSSFWorkbook();//实例化excel对象
                            ISheet sheet = book.CreateSheet(string.Format("{0}点", k.ToString()));//创建sheet工作表
                            IRow row0 = sheet.CreateRow(0);//创建一行（表头）

                            ICell cell0 = row0.CreateCell(0);
                            cell0.SetCellType(CellType.String);
                            cell0.SetCellValue(number);

                            ICell cell1 = row0.CreateCell(1);
                            cell1.SetCellType(CellType.String);
                            cell1.SetCellValue(dataItem);

                            ICell cell2 = row0.CreateCell(2);
                            cell2.SetCellType(CellType.String);
                            cell2.SetCellValue(time);

                            var filewrite = new FileStream(excelpath, FileMode.Create, FileAccess.ReadWrite);//覆盖创建的excel文件
                            //BufferedStream bs = new BufferedStream(filewrite,1024*1024*5);
                            //book.Write(bs);
                            book.Write(filewrite);
                            filewrite.Dispose();
                            #endregion
                        }
                    }
                }
            }
        }
        public static void ReadSpeCell( int rowcount,
                                             int cellcount,ref double value)
        {

            string finalpath ="shuju.xls";
            FileStream filestreamwrite = new FileStream(finalpath, FileMode.Open, FileAccess.Read);
            //2从读取文件流中创建一个HSSFWorkbook
            HSSFWorkbook book = new HSSFWorkbook(filestreamwrite);
            HSSFSheet sheet = book.GetSheetAt(0) as HSSFSheet;
            IRow headerrow = sheet.GetRow(rowcount);
            HSSFCell cell = headerrow.GetCell(cellcount) as HSSFCell;
            value = cell.NumericCellValue;
            filestreamwrite.Dispose();
            filestreamwrite.Close();
        }
        public static void ReadSpeCell(int rowcount,
                                             int cellcount, ref double value,string name)
        {

            string finalpath = "shuju.xls";
            FileStream filestreamwrite = new FileStream(finalpath, FileMode.Open, FileAccess.Read);
            //2从读取文件流中创建一个HSSFWorkbook
            HSSFWorkbook book = new HSSFWorkbook(filestreamwrite);
            HSSFSheet sheet = book.GetSheetAt(1) as HSSFSheet;
            IRow headerrow = sheet.GetRow(rowcount);
            HSSFCell cell = headerrow.GetCell(cellcount) as HSSFCell;
            value = cell.NumericCellValue;
            filestreamwrite.Dispose();
            filestreamwrite.Close();
        }
        /// <summary>
        /// 将数据写入到MYEXCEL数据库
        /// </summary>
        /// <param name="statustowrite">要写入的数据（字符或字符串）</param>
        /// <param name="sheetrowcount">当前工作簿已经写入的数据行数</param>
        public static void WriteMYEXCEL(string statustowrite, out int sheetrowcount)
        {
            sheetrowcount = 0;
            int rowcount = 0;
            string finalpath = Path.Combine(Directory.GetCurrentDirectory(), DateTime.Now.Year + "年读码数据库", DateTime.Now.Month + "月", DateTime.Now.Day + "日", DateTime.Now.Hour + "点" + ".xls");
            FileStream filestreamwrite = new FileStream(finalpath, FileMode.Open, FileAccess.ReadWrite);
            //2从读取文件流中创建一个HSSFWorkbook
            HSSFWorkbook book = new HSSFWorkbook(filestreamwrite);
            //3 获取哪一个表格Sheet
            ISheet sheet = book.GetSheetAt(0);
            //var st = sheet as Excel.Workbook; 我们也可以由此联想到可以将表格转化为Excel表格
            //进而显示到datagridview控件上
            //4 获取当前表格的所有的行
            IEnumerator rows = sheet.GetRowEnumerator();//得到一个迭代器
            bool Isendofcell = false;
            while (rows.MoveNext()) //不断遍历 往下读取 直到最后
            {
                if (Isendofcell)
                {
                    break;
                }
                HSSFRow rowenum = rows.Current as HSSFRow;
                IEnumerator cells = rowenum.GetEnumerator();
                while (cells.MoveNext())
                {
                    ICell cell = cells.Current as ICell;
                    //string value = cell.ToString();
                    if (cell.ToString().Length == 0)
                    {
                        Isendofcell = true;
                        break;
                    }
                    else
                    {
                        rowcount++;
                    }

                }
            }
            //迭代完成后，追加新的一行

            IRow newrow = sheet.CreateRow(rowcount / 3);
            //this.BeginInvoke(new MethodInvoker(delegate
            //{
            sheetrowcount = rowcount / 3;
            //}));


            ICell cellnumber = newrow.CreateCell(0);
            ICell celldecoded = newrow.CreateCell(1);
            ICell celltime = newrow.CreateCell(2);

            cellnumber.SetCellValue(sheetrowcount);//行号
            celldecoded.SetCellValue(statustowrite);//数据
            celltime.SetCellValue(string.Format("{0}时{1}分{2}秒", DateTime.Now.Hour.ToString(), DateTime.Now.Minute.ToString(), DateTime.Now.Second.ToString()));//时间
            filestreamwrite.Dispose();
            //修改完成后，写回到数据库     
            FileStream filestreamsave = new FileStream(finalpath, FileMode.Open, FileAccess.Write);
            book.Write(filestreamsave);
            filestreamsave.Dispose();
        }

        public static bool CheckExcelOK()
        {
            bool tempflag = false;
            string yearpath = Path.Combine(Directory.GetCurrentDirectory(), DateTime.Now.Year + "年读码数据库");
            string monthpath = Path.Combine(yearpath, "12月");
            string daypath = Path.Combine(monthpath, "31日");
            string hourpath = Path.Combine(daypath, "23点.xls");
            if (File.Exists(hourpath))
            {
                tempflag = true;
            }
            else
            {
                tempflag = false;
            }
            return tempflag;
        }
    }
}
